﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;
using CTEM.Models;
using CTEM.Controllers.Global;
using System.Diagnostics;
using CTEM.Controllers.Global;

namespace CTEM.Controllers.DAO
{
    public class CS_ConstructionDAO
    {
        /// <summary>
        /// 
        /// </summary>
        public CS_ConstructionDAO()
        {
            Transaction = null;
        }

        /// <summary>
        /// 
        /// </summary>
        public SqlTransaction Transaction { get; set; }

        /// <summary>
        /// 
        /// </summary>
        private StackFrame _currentStackFrame;

        /// <summary>
        /// 
        /// </summary>
        private SqlConnection _con;

        /// <summary>
        /// 
        /// </summary>
        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cons"></param>
        /// <returns></returns>
        public long CreateConstruction(CS_ConstructionDTO cons)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_CreateConstruction]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@ConstructionName", 
                                                  "@ContractValues", 
                                                  "@ConstructionType", 
                                                  "@Description", 
                                                  "@ConstructionAddress", 
                                                  "@CommencementDate", 
                                                  "@CompletionDate", 
                                                  "@ManagerID", 
                                                  "@Status", 
                                                  "@ProgressRate", 
                                                  "@IsActive",
                                                  "@IsDelete",
                                                  "@CreatedBy",
                                                  "@CreatedDate" };
                    object[] parametervalues = { cons.ConstructionName, 
                                                   cons.ContractValues, 
                                                   cons.ConstructionType, 
                                                   cons.Description, 
                                                   cons.ConstructionAddress, 
                                                   cons.CommencementDate, 
                                                   cons.CompletionDate, 
                                                   cons.ManagerID, 
                                                   cons.Status, 
                                                   cons.ProgressRate, 
                                                   cons.IsActive,
                                                   cons.IsDelete,
                                                   cons.CreatedBy,
                                                   cons.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cons"></param>
        /// <returns></returns>
        public bool UpdateConstruction(CS_ConstructionDTO cons)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_UpdateConstruction]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@ConstructionID", 
                                                  "@ConstructionName", 
                                                  "@ContractValues", 
                                                  "@ConstructionType", 
                                                  "@Description", 
                                                  "@ConstructionAddress", 
                                                  "@CommencementDate", 
                                                  "@CompletionDate", 
                                                  "@ManagerID", 
                                                  "@Status", 
                                                  "@ProgressRate", 
                                                  "@IsActive",
                                                  "@IsDelete",
                                                  "@UpdatedBy",
                                                  "@LastUpdated" };
                    object[] parametervalues = { cons.ConstructionID, 
                                                   cons.ConstructionName, 
                                                   cons.ContractValues, 
                                                   cons.ConstructionType, 
                                                   cons.Description, 
                                                   cons.ConstructionAddress, 
                                                   cons.CommencementDate, 
                                                   cons.CompletionDate, 
                                                   cons.ManagerID, 
                                                   cons.Status, 
                                                   cons.ProgressRate, 
                                                   cons.IsActive,
                                                   cons.IsDelete,
                                                   cons.UpdatedBy,
                                                   cons.LastUpdated };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consDTO"></param>
        /// <returns></returns>
        public bool DeleteConstruction(long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_DeleteConstruction]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@ConstructionID", consID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consID"></param>
        /// <returns></returns>
        public CS_ConstructionDTO GetConstructionByID(long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetConstructionByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ConstructionID", consID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            CS_ConstructionDTO consDTO = new CS_ConstructionDTO
                            {
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                ConstructionName = Convert.ToString(reader["ConstructionName"]),
                                ContractValues = Convert.ToDouble(reader["ContractValues"]),
                                ConstructionType = Convert.ToString(reader["ConstructionType"]),
                                Description = Convert.ToString(reader["Description"]),
                                ConstructionAddress = Convert.ToString(reader["ConstructionAddress"]),
                                CommencementDate = Convert.ToDateTime(reader["CommencementDate"]),
                                CompletionDate = Convert.ToDateTime(reader["CompletionDate"]),
                                ManagerID = Convert.ToInt64(reader["ManagerID"]),
                                Status = Convert.ToString(reader["Status"]),
                                ProgressRate = Convert.ToDouble(reader["ProgressRate"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            return consDTO;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isActive"></param> 
        /// <returns></returns>
        public List<CS_ConstructionDTO> LoadAllConstructions(bool isActive)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetAllConstruction]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                if (isActive)
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", isActive));
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", DBNull.Value));
                }
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CS_ConstructionDTO> listcons = new List<CS_ConstructionDTO>();
                        while (reader.Read())
                        {
                            CS_ConstructionDTO consDTO = new CS_ConstructionDTO
                            {
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                ConstructionName = Convert.ToString(reader["ConstructionName"]),
                                ContractValues = Convert.ToDouble(reader["ContractValues"]),
                                ConstructionType = Convert.ToString(reader["ConstructionType"]),
                                Description = Convert.ToString(reader["Description"]),
                                ConstructionAddress = Convert.ToString(reader["ConstructionAddress"]),
                                CommencementDate = Convert.ToDateTime(reader["CommencementDate"]),
                                CompletionDate = Convert.ToDateTime(reader["CompletionDate"]),
                                ManagerID = Convert.ToInt64(reader["ManagerID"]),
                                Status = Convert.ToString(reader["Status"]),
                                ProgressRate = Convert.ToDouble(reader["ProgressRate"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            listcons.Add(consDTO);
                        }
                        return listcons;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consprogress"></param>
        /// <returns></returns>
        public long CreateConstructionProgress(CS_ConstructionProgressDTO consprogress)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_CreateConstructionProgress]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@ConstructionID",
                                                  "@Reporter",
                                                  "@Progress",
                                                  "@Date",
                                                  "@Note",
                                                  "@IsDelete" };
                    object[] parametervalues = { consprogress.ConstructionID, 
                                                   consprogress.Reporter, 
                                                   consprogress.Progress, 
                                                   consprogress.Date, 
                                                   consprogress.Note, 
                                                   consprogress.IsDelete };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consprogress"></param>
        /// <returns></returns>
        public bool UpdateConstructionProgress(CS_ConstructionProgressDTO consprogress)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_UpdateConstructionProgress]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@ConstructionProgressID",
                                                  "@ConstructionID",
                                                  "@Reporter",
                                                  "@Progress",
                                                  "@Date",
                                                  "@Note",
                                                  "@IsDelete" };
                    object[] parametervalues = { consprogress.ConstructionProgressID,
                                                   consprogress.ConstructionID, 
                                                   consprogress.Reporter, 
                                                   consprogress.Progress, 
                                                   consprogress.Date, 
                                                   consprogress.Note, 
                                                   consprogress.IsDelete };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consDTO"></param>
        /// <returns></returns>
        public bool DeleteConstructionProgress(long consprogressID, long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_DeleteConstructionProgress]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@ConstructionProgressID", consprogressID));
                    cmd.Parameters.Add(new SqlParameter("@ConstructionID", consID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="consID"></param>
        /// <returns></returns>
        public CS_ConstructionProgressDTO GetConstructionProgressByID(long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetConstructionProgressByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ConstructionProgressID", consID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            CS_ConstructionProgressDTO consDTO = new CS_ConstructionProgressDTO
                            {
                                ConstructionProgressID = Convert.ToInt64(reader["ConstructionProgressID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                Reporter = Convert.ToString(reader["Reporter"]),
                                Progress = Convert.ToDouble(reader["Progress"]),
                                Date = Convert.ToDateTime(reader["Date"]),
                                Note = Convert.ToString(reader["Note"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"])
                            };
                            return consDTO;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isActive"></param> 
        /// <returns></returns>
        public List<CS_ConstructionProgressDTO> LoadConstructionProgresss(long consID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[CS_GetConstructionProgress]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@ConstructionID", consID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<CS_ConstructionProgressDTO> listcons = new List<CS_ConstructionProgressDTO>();
                        while (reader.Read())
                        {
                            CS_ConstructionProgressDTO consDTO = new CS_ConstructionProgressDTO
                            {
                                ConstructionProgressID = Convert.ToInt64(reader["ConstructionProgressID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                Reporter = Convert.ToString(reader["Reporter"]),
                                Progress = Convert.ToDouble(reader["Progress"]),
                                Date = Convert.ToDateTime(reader["Date"]),
                                Note = Convert.ToString(reader["Note"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"])
                            };
                            listcons.Add(consDTO);
                        }
                        return listcons;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
    }
}
